Course: MySQL Training
Topic: Introduction to Aggregate Functions
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
Aggregate functions are built-in MySQL functions that perform calculations on multiple rows or a collection of values and return a single result. They are widely used in database reporting, business analysis, financial calculations, sales summaries, employee statistics, inventory management, and data analytics.
For example, a company may store thousands of sales transactions in a database. Instead of retrieving every transaction and calculating the total manually, an aggregate function can calculate the total revenue directly from the table. Similarly, a school can calculate the average marks of students, an organization can count its employees, and a store can identify its highest and lowest product prices.
The most commonly used MySQL aggregate functions are:
COUNT() — counts rows or non-NULL values.SUM() — calculates the total of numeric values.AVG() — calculates the average of numeric values.MIN() — returns the minimum value.MAX() — returns the maximum value.Aggregate functions are frequently combined with SQL clauses such as WHERE, GROUP BY, HAVING, and ORDER BY to create useful summaries and reports.
After completing this topic, learners should be able to:
An aggregate function processes multiple values and returns one summarized result. Depending on the query, that result may represent an entire table, a filtered set of rows, or each group created by GROUP BY.
For example, consider a table containing the following sales amounts:
| Sale ID | Product | Amount |
|---|---|---|
| 1 | Laptop | 50000 |
| 2 | Mouse | 1000 |
| 3 | Keyboard | 2000 |
| 4 | Monitor | 15000 |
The SUM() function can calculate the total sales amount of these four records, while COUNT() can count the records and AVG() can calculate their average amount.
SELECT
COUNT(*) AS total_sales,
SUM(amount) AS total_revenue,
AVG(amount) AS average_sale,
MIN(amount) AS smallest_sale,
MAX(amount) AS largest_sale
FROM sales;This query returns one row containing five summarized values instead of returning all four individual sales records.
| Function | Purpose | Example |
|---|---|---|
| COUNT() | Counts rows or non-NULL values. | COUNT(*) |
| SUM() | Adds numeric values. | SUM(salary) |
| AVG() | Calculates the average of numeric values. | AVG(marks) |
| MIN() | Returns the smallest value. | MIN(price) |
| MAX() | Returns the largest value. | MAX(price) |
| COUNT(DISTINCT) | Counts unique non-NULL values or combinations. | COUNT(DISTINCT city) |
| GROUP_CONCAT() | Combines non-NULL values into a string. | GROUP_CONCAT(product_name) |
This topic focuses mainly on COUNT(), SUM(), AVG(), MIN(), and MAX(), which form the foundation of aggregate calculations in MySQL.
To understand aggregate functions, create a sample employee table and insert some records. The same table will be used throughout these notes.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2),
bonus DECIMAL(10,2),
city VARCHAR(50)
);The table contains employee details, including department, salary, bonus, and city.
INSERT INTO employees
(employee_id, employee_name, department, salary, bonus, city)
VALUES
(1, 'Aarav', 'IT', 50000, 5000, 'Mumbai'),
(2, 'Priya', 'HR', 40000, 3000, 'Pune'),
(3, 'Rohan', 'IT', 60000, NULL, 'Mumbai'),
(4, 'Neha', 'Finance', 55000, 4000, 'Delhi'),
(5, 'Kabir', 'HR', 35000, NULL, 'Pune'),
(6, 'Meera', 'Finance', 65000, 6000, 'Mumbai'),
(7, 'Ishaan', 'IT', 45000, 2000, 'Delhi'),
(8, 'Ananya', 'Sales', 48000, 3500, 'Pune');SELECT *
FROM employees;The table now contains eight employee records. Some employees have NULL bonus values. These NULL values will help demonstrate how aggregate functions handle missing data.
The COUNT() function counts rows or values according to the expression supplied. It is commonly used to calculate the number of employees, customers, orders, products, or other records in a database.
COUNT(*)
COUNT(expression)
COUNT(DISTINCT expression)COUNT(*) counts all rows included in the query result, regardless of whether individual columns contain NULL values.
SELECT COUNT(*) AS total_employees
FROM employees;Expected result: 8
This query counts all eight employee records.
COUNT(column) counts rows where the specified column is not NULL. Rows containing NULL in that column are excluded from the count.
SELECT COUNT(bonus) AS employees_with_bonus_value
FROM employees;Expected result: 6
Only six employees have a non-NULL bonus value. An employee whose bonus is zero would still be counted because zero is not NULL.
COUNT(DISTINCT column) counts unique non-NULL values in a column. Duplicate values are counted only once.
SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;Expected result: 4
The departments are IT, HR, Finance, and Sales. Each department is counted once, even when multiple employees belong to the same department.
COUNT() can be combined with WHERE to count records matching a particular condition.
SELECT COUNT(*) AS it_employees
FROM employees
WHERE department = 'IT';Expected result: 3
This query counts employees who belong to the IT department.
SELECT
city,
COUNT(*) AS employee_count
FROM employees
GROUP BY city;This query calculates the number of employees in each city. Mumbai, Pune, and Delhi appear as separate groups.
The SUM() function calculates the total of numeric values in a column or expression. It is commonly used for total salaries, sales revenue, expenses, quantities sold, bonuses, and order amounts.
SUM(expression)
SUM(DISTINCT expression)SELECT SUM(salary) AS total_salary
FROM employees;Expected result: 398000.00
The query adds the salaries of all eight employees.
SELECT SUM(bonus) AS total_bonus
FROM employees;Expected result: 23500.00
SUM(bonus) ignores NULL bonus values and adds the available numeric values. If every bonus value were NULL, SUM(bonus) would return NULL.
SELECT SUM(salary) AS it_total_salary
FROM employees
WHERE department = 'IT';Expected result: 155000.00
This query calculates the combined salaries of the three IT employees.
GROUP BY divides records into groups. SUM() then calculates a separate total for each group.
SELECT
department,
SUM(salary) AS department_salary
FROM employees
GROUP BY department;This query returns one row per department with its total salary. It is useful for departmental budget reports and payroll analysis.
SUM(DISTINCT expression) adds only distinct numeric values. Duplicate values are included once rather than being added repeatedly.
SELECT SUM(DISTINCT salary) AS distinct_salary_total
FROM employees;Use SUM(DISTINCT) only when removing duplicate numeric values is part of the intended calculation. It is not a general way to eliminate duplicate records from a report because different employees can legitimately have the same salary.
The AVG() function calculates the arithmetic mean of numeric values. It adds the non-NULL values and divides the total by the number of non-NULL values.
For example, if three students score 70, 80, and 90, their average score is:
(70 + 80 + 90) / 3 = 80AVG(expression)
AVG(DISTINCT expression)SELECT AVG(salary) AS average_salary
FROM employees;Expected result: 49750.00
The query calculates the average salary of all eight employees.
SELECT AVG(bonus) AS average_bonus
FROM employees;Expected result: approximately 3916.67
AVG(bonus) excludes NULL bonus values from both the sum and the count used in the average. It does not treat NULL as zero automatically.
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;This query calculates the average salary separately for IT, HR, Finance, and Sales.
AVG(DISTINCT expression) calculates the average using distinct non-NULL numeric values only.
SELECT AVG(DISTINCT salary) AS average_distinct_salary
FROM employees;It is useful only when the business question specifically concerns distinct values rather than every employee's salary.
The MIN() function returns the smallest value in the selected set of values. It is useful for finding the lowest salary, lowest product price, earliest date, or minimum recorded score.
MIN(expression)
MIN(DISTINCT expression)SELECT MIN(salary) AS lowest_salary
FROM employees;Expected result: 35000.00
Kabir has the lowest salary among the employees in the sample table.
SELECT
department,
MIN(salary) AS minimum_salary
FROM employees
GROUP BY department;This query returns the minimum salary for each department.
MIN() is not limited to numeric columns. For text, it returns the minimum according to the applicable string comparison and collation rules. For dates, it returns the earliest date value.
SELECT MIN(employee_name) AS minimum_name
FROM employees;SELECT MIN(hire_date) AS earliest_hire_date
FROM employees;The second query assumes that the employees table contains a hire_date column.
The MAX() function returns the largest value in a selected set. It is frequently used to identify the highest salary, largest order, most expensive product, latest date, or highest examination score.
MAX(expression)
MAX(DISTINCT expression)SELECT MAX(salary) AS highest_salary
FROM employees;Expected result: 65000.00
Meera has the highest salary among the employees in the sample table.
SELECT
department,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;This query identifies the highest salary in each department.
MAX() can be used with a date column to find the latest recorded date.
SELECT MAX(order_date) AS latest_order_date
FROM orders;This query assumes that an orders table with an order_date column exists.
| Function | What It Returns | Example | Business Use |
|---|---|---|---|
| COUNT() | Number of rows or non-NULL values. | COUNT(*) |
Count employees or orders. |
| SUM() | Total of numeric values. | SUM(salary) |
Calculate payroll expenses. |
| AVG() | Average of numeric values. | AVG(salary) |
Calculate average salary. |
| MIN() | Smallest value. | MIN(salary) |
Find the lowest salary. |
| MAX() | Largest value. | MAX(salary) |
Find the highest salary. |
These functions can be used together in one SELECT query to generate a compact summary of a dataset.
SELECT
COUNT(*) AS total_employees,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees;NULL represents a missing or unknown value. Most MySQL aggregate functions ignore NULL values when calculating results. COUNT(*) is an important exception because it counts rows regardless of whether columns contain NULL.
| Function or Expression | Behavior with NULL |
|---|---|
COUNT(*) |
Counts every row. |
COUNT(column) |
Counts only non-NULL values in that column. |
SUM(column) |
Ignores NULL values; returns NULL if there are no non-NULL values to sum. |
AVG(column) |
Ignores NULL values when calculating the average. |
MIN(column) |
Ignores NULL values when finding the minimum; returns NULL if no non-NULL value is available. |
MAX(column) |
Ignores NULL values when finding the maximum; returns NULL if no non-NULL value is available. |
SELECT
COUNT(*) AS all_rows,
COUNT(bonus) AS known_bonus_values
FROM employees;The first expression counts all eight rows. The second counts only the six rows whose bonus is not NULL.
When no non-NULL values are available, SUM() can return NULL. If the business requirement calls for displaying zero, IFNULL() can provide a fallback.
SELECT
IFNULL(SUM(bonus), 0) AS total_bonus
FROM employees
WHERE department = 'Marketing';This query returns zero if no matching bonus values are available. Remember that a fallback value changes the displayed result and should match the business meaning of the report.
The WHERE clause filters individual rows before the aggregate function calculates its result. This is useful when you need a summary for a specific department, city, date range, or category.
SELECT SUM(salary) AS it_total_salary
FROM employees
WHERE department = 'IT';SELECT AVG(salary) AS average_salary
FROM employees
WHERE salary >= 45000;Only employees whose salary is at least 45000 are included in the calculation.
SELECT COUNT(*) AS mumbai_employees
FROM employees
WHERE city = 'Mumbai';SELECT MAX(salary) AS finance_highest_salary
FROM employees
WHERE department = 'Finance';GROUP BY organizes rows with the same value in one or more columns into groups. Aggregate functions then calculate a result for each group.
For example, instead of calculating one total salary for the entire company, GROUP BY department calculates a separate total for IT, HR, Finance, and Sales.
SELECT
grouping_column,
aggregate_function(expression)
FROM table_name
GROUP BY grouping_column;SELECT
department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department;SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;SELECT
city,
AVG(salary) AS average_salary
FROM employees
GROUP BY city;SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;This query creates a detailed salary summary for every department.
HAVING filters groups after GROUP BY and aggregate calculations have been applied. It is useful when you want to display only groups that meet a condition based on their totals, averages, or counts.
SELECT
grouping_column,
aggregate_function(expression)
FROM table_name
GROUP BY grouping_column
HAVING aggregate_condition;SELECT
department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;Only departments whose total salary exceeds 100000 appear in the result.
SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 45000;| WHERE | HAVING |
|---|---|
| Filters individual rows before grouping. | Filters groups after grouping. |
| Usually applies conditions to ordinary column values. | Commonly applies conditions to aggregate results. |
Example: WHERE city = 'Mumbai' |
Example: HAVING SUM(salary) > 100000 |
ORDER BY sorts the rows returned by an aggregate query. You can sort by a grouping column or by an aggregate result.
SELECT
department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;DESC sorts the results from highest to lowest total salary. Use ASC for ascending order.
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;SELECT
department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC
LIMIT 2;LIMIT restricts the number of rows in the final result. In this query, the two departments with the highest total salaries are returned.
DISTINCT can be used within several aggregate functions to remove duplicate expression values before the calculation.
SELECT COUNT(DISTINCT city) AS unique_cities
FROM employees;The result is 3 because the employees are located in Mumbai, Pune, and Delhi.
SELECT SUM(DISTINCT salary) AS total_distinct_salaries
FROM employees;This query adds each distinct salary value once. If two employees earn the same salary, that salary is included only once in the result.
SELECT AVG(DISTINCT salary) AS average_distinct_salary
FROM employees;This calculates the average of unique salary values rather than the average salary across all employees. Understand the business question before using DISTINCT in a calculation.
Conditional aggregation calculates summaries for rows that meet specified conditions. MySQL's IF() or CASE expressions can be used inside aggregate functions to build flexible reports.
SELECT
COUNT(*) AS total_employees,
SUM(CASE WHEN department = 'IT' THEN 1 ELSE 0 END) AS it_employees,
SUM(CASE WHEN department = 'HR' THEN 1 ELSE 0 END) AS hr_employees,
SUM(CASE WHEN department = 'Finance' THEN 1 ELSE 0 END) AS finance_employees
FROM employees;The query returns one row with the total employee count and separate counts for IT, HR, and Finance.
SELECT
SUM(CASE WHEN department = 'IT' THEN salary ELSE 0 END) AS it_salary,
SUM(CASE WHEN department = 'HR' THEN salary ELSE 0 END) AS hr_salary,
SUM(CASE WHEN department = 'Finance' THEN salary ELSE 0 END) AS finance_salary
FROM employees;Conditional aggregation is useful when building compact dashboards and reports with several related metrics.
SELECT
COUNT(*) AS total_employees,
COUNT(bonus) AS employees_with_known_bonus,
SUM(CASE WHEN bonus > 0 THEN 1 ELSE 0 END) AS employees_with_positive_bonus
FROM employees;These three expressions answer different questions. COUNT(*) counts all employees, COUNT(bonus) counts non-NULL bonus values, and the conditional SUM counts employees whose bonus is greater than zero.
Aggregate functions behave differently depending on the type of data being processed.
SUM() and AVG() are intended for numeric calculations. They should not be used directly on date or time values to calculate meaningful date/time totals. For temporal calculations, convert values into appropriate numeric units first when necessary.
| Mistake | Problem | Correct Approach |
|---|---|---|
| Using COUNT(column) to count every row | NULL values in that column are excluded. | Use COUNT(*) to count rows. |
| Assuming SUM() treats NULL as zero in every situation | SUM() ignores NULL values and may return NULL when no non-NULL input is available. | Use IFNULL(SUM(column), 0) when zero is the correct reporting fallback. |
| Using WHERE with an aggregate condition | WHERE filters rows before aggregation and generally cannot be used to filter an aggregate result in the same query block. | Use HAVING for aggregate conditions. |
| Forgetting GROUP BY | Results may be summarized across all rows rather than by category. | Add GROUP BY when separate results are needed for each category. |
| Using SUM(DISTINCT) without checking the business meaning | Legitimate repeated values may be counted only once. | Use DISTINCT only when unique values are the intended input. |
| Selecting non-grouped columns in aggregate queries | Depending on SQL mode and query structure, MySQL may reject the query or return an unsuitable value. | Select grouping columns and aggregate expressions, or use a valid functional dependency. |
| Assuming AVG() includes NULL as zero | NULL values are excluded from the average. | Use an explicit expression such as AVG(IFNULL(column, 0)) only if missing values should count as zero. |
| Misreading MIN() or MAX() on text | String comparison follows collation and comparison rules rather than numeric meaning. | Use an appropriate numeric column or explicit conversion when required. |
If an aggregate query returns unexpected results, check the following:
total_salary, average_sales, and employee_count.Project Objective: Create a department-level employee report using aggregate functions in MySQL.
SELECT
COUNT(*) AS total_employees,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary,
SUM(bonus) AS total_known_bonus,
AVG(bonus) AS average_known_bonus
FROM employees;SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY total_salary DESC;After completing this project, learners will understand how aggregate functions work together to produce company-wide and department-level summaries. They will also gain practical experience with NULL values, grouping, filtering, and sorting summarized results.
Use the sample employees table to complete the following tasks:
Solution 1: Count all employees
SELECT COUNT(*) AS total_employees
FROM employees;Solution 2: Count non-NULL bonuses
SELECT COUNT(bonus) AS known_bonus_count
FROM employees;Solution 3: Count unique cities
SELECT COUNT(DISTINCT city) AS unique_cities
FROM employees;Solution 4: Total salary
SELECT SUM(salary) AS total_salary
FROM employees;Solution 5: Average salary
SELECT AVG(salary) AS average_salary
FROM employees;Solution 6: Lowest salary
SELECT MIN(salary) AS lowest_salary
FROM employees;Solution 7: Highest salary
SELECT MAX(salary) AS highest_salary
FROM employees;Solution 8: Total salary for HR
SELECT SUM(salary) AS hr_total_salary
FROM employees
WHERE department = 'HR';Solution 9: Average salary in Mumbai
SELECT AVG(salary) AS mumbai_average_salary
FROM employees
WHERE city = 'Mumbai';Solution 10: Employee count by department
SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;Solution 11: Departments with total salary above 100000
SELECT
department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;Solution 12: Departments with average salary above 45000
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 45000;Solution 13: Sort by employee count
SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;Solution 14: Total salaries for IT and Finance
SELECT
SUM(CASE WHEN department = 'IT' THEN salary ELSE 0 END) AS it_salary,
SUM(CASE WHEN department = 'Finance' THEN salary ELSE 0 END) AS finance_salary
FROM employees;Solution 15: Compare total rows and known bonuses
SELECT
COUNT(*) AS total_rows,
COUNT(bonus) AS non_null_bonus_rows
FROM employees;An aggregate function performs a calculation on multiple values or rows and returns a summarized result.
COUNT(), SUM(), AVG(), MIN(), and MAX().
COUNT() counts rows or non-NULL values, depending on the expression supplied.
COUNT(*) counts all rows, whereas COUNT(column) counts only rows where that column is not NULL.
SUM() calculates the total of numeric values, excluding NULL values.
AVG() calculates the arithmetic mean by dividing the sum of non-NULL values by the number of non-NULL values.
MIN() returns the smallest value in the selected set.
MAX() returns the largest value in the selected set.
Most aggregate functions ignore NULL values. COUNT(*) counts rows regardless of NULL values in individual columns.
SUM() returns NULL when there are no non-NULL input values, including when the input set contains no rows.
GROUP BY groups rows with matching values so aggregate functions can calculate separate results for each group.
WHERE filters individual rows before aggregation. HAVING filters groups based on aggregate conditions after grouping.
It returns the number of distinct non-NULL city values.
Yes. MIN() can return the earliest date and MAX() can return the latest date in the selected set.
Conditional aggregation uses expressions such as CASE inside aggregate functions to calculate summaries for records that meet particular conditions.
ORDER BY sorts the summarized result, such as arranging departments by total salary from highest to lowest.
It adds each distinct non-NULL salary value once. Duplicate salary values are not added repeatedly.
They summarize large datasets into useful metrics, such as totals, counts, averages, minimums, and maximums, making reports easier to interpret.
Aggregate functions are essential tools for analyzing and summarizing data in MySQL. COUNT(), SUM(), AVG(), MIN(), and MAX() make it possible to calculate record counts, totals, averages, and value ranges without manually processing every row.
When combined with WHERE, GROUP BY, HAVING, ORDER BY, and DISTINCT, aggregate functions support more advanced queries and meaningful business reports. Understanding how these functions treat NULL values is equally important for producing accurate results.
Practice the examples and exercises in these notes to develop confidence in writing aggregate queries for employee management, sales reporting, finance, inventory, and other database applications.
